# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   File Name：     run_heating_V03
   Description :
   Author :       dx964
   date：          2022/3/8
-------------------------------------------------
   Change Activity:
                   2022/3/8:
-------------------------------------------------
    Details :   1、对重复品番进行提示！
    
    
-------------------------------------------------   
"""
import datetime
import shutil
import tkinter as tk
import tkinter.ttk
from tkinter import messagebox
from openpyxl import load_workbook, Workbook
import sqlite3
import os

# 连接SqlLite数据库
from openpyxl.styles import Side, Border

conn = sqlite3.connect('database.db')

# 创建窗口
windows = tk.Tk()
windows.title("成型加热确认表")
windows.geometry("640x400+180+80") # 窗口大小及位置
windows.resizable(0,0)

# 定义Label ：设定条件
label_condition = tk.Label(windows,
                           text='请选择设定条件：',  # 标签的文字
                           # bg='green',  # 标签背景颜色
                           font=('Arial', 12),  # 字体和字体大小
                           width=15, height=2  # 标签长宽(以字符长度计算)
                           )
label_condition.grid(row=0, column=0)  # 固定窗口位置

# 定义下拉框Combobox：用来存储设定条件
# cbox_condition_value = ''
cbox_condition1 = tkinter.ttk.Combobox(windows)
cbox_condition1.grid(row=0, column=1)
cbox_condition2 = tkinter.ttk.Combobox(windows)
cbox_condition2.grid(row=0, column=2)
# 连接数据库获取下拉框值
# 下拉框1
c = conn.cursor()
times = []
cursor = c.execute("SELECT times FROM heating_base")
for row in cursor:
    times.append(row[0])
times = list(set(times))  # 使用内置函数set去除列表中的重复值
times.sort()
# heating_time.insert(0,'')
cbox_condition1['value'] = times  # 下拉框的值

pinfan = []
# 编写下拉框的回调函数1，用来绑定执行事件
def func_condition2(event):
    global pinfan
    cbox_condition_value1 = cbox_condition1.get()
    cbox_condition_value2 = cbox_condition2.get()
    # print(cbox_condition_value1)
    conn = sqlite3.connect('database.db')
    c = conn.cursor()
    cursor = c.execute("SELECT pinfan,heating_time FROM heating_base WHERE times='%s' AND temperature = '%s'" %(cbox_condition_value1,cbox_condition_value2))
    pinfan = []
    heating_time = []
    for row in cursor:  # 从数据库获取设定条件参数
        # print("heating_time:", row[0])
        pinfan.append(row[0])
        heating_time.append(row[1])
    pinfan = list(set(pinfan))
    heating_time = list(set(heating_time))
    # print(heating_time)
    pinfan.insert(0,'')
    # print(pinfan)
    # 把品番设定为下拉框的值
    cbox_pinfan1['value'] = pinfan
    cbox_pinfan1.current(0)  # 下拉框的默认值
    entry_batch1.delete(0, tk.END)  # 删除批号输入框内的所有值
    entry_quantity1.delete(0, tk.END)   # 删除数量输入框内的所有值
    cbox_pinfan2['value']=pinfan
    cbox_pinfan2.current(0)  # 下拉框的默认值
    entry_batch2.delete(0, tk.END)  # 删除批号输入框内的所有值
    entry_quantity2.delete(0, tk.END)   # 删除数量输入框内的所有值
    cbox_pinfan3['value']=pinfan
    cbox_pinfan3.current(0)  # 下拉框的默认值
    entry_batch3.delete(0, tk.END)  # 删除批号输入框内的所有值
    entry_quantity3.delete(0, tk.END)   # 删除数量输入框内的所有值
    cbox_pinfan4['value']=pinfan
    cbox_pinfan4.current(0)  # 下拉框的默认值
    entry_batch4.delete(0, tk.END)  # 删除批号输入框内的所有值
    entry_quantity4.delete(0, tk.END)   # 删除数量输入框内的所有值
    cbox_pinfan5['value']=pinfan
    cbox_pinfan5.current(0)  # 下拉框的默认值
    entry_batch5.delete(0, tk.END)  # 删除批号输入框内的所有值
    entry_quantity5.delete(0, tk.END)   # 删除数量输入框内的所有值
    cbox_pinfan6['value']=pinfan
    cbox_pinfan6.current(0)  # 下拉框的默认值
    entry_batch6.delete(0, tk.END)  # 删除批号输入框内的所有值
    entry_quantity6.delete(0, tk.END)   # 删除数量输入框内的所有值
    cbox_pinfan7['value']=pinfan
    cbox_pinfan7.current(0)  # 下拉框的默认值
    entry_batch7.delete(0, tk.END)  # 删除批号输入框内的所有值
    entry_quantity7.delete(0, tk.END)   # 删除数量输入框内的所有值
    cbox_pinfan8['value']=pinfan
    cbox_pinfan8.current(0)  # 下拉框的默认值
    entry_batch8.delete(0, tk.END)  # 删除批号输入框内的所有值
    entry_quantity8.delete(0, tk.END)   # 删除数量输入框内的所有值
    cbox_time_base['value'] = heating_time  # 升温时间基准 下拉框的值
    cbox_time_base.current(0)

    conn.close()

# 编写下拉框的回调函数2，使前面下拉框选择好时间后，后面温度的下拉框只会显示满足条件的值
def func_condition1(event):
    cbox_condition_value1 = cbox_condition1.get()
    conn = sqlite3.connect('database.db')
    c = conn.cursor()
    # 下拉框2-温度
    temperature = []
    cursor = c.execute("SELECT temperature FROM heating_base WHERE times='%s'" % cbox_condition_value1)
    for row in cursor:
        temperature.append(row[0])
    temperature = list(set(temperature))  # 使用内置函数set去除列表中的重复值
    temperature.sort()
    temperature.insert(0,'')
    # print(temperature)
    cbox_condition2['value'] = temperature  # 下拉框的值
    cbox_condition2.current(0)

# 把回调函数与下拉框进行绑定
cbox_condition1.bind("<<ComboboxSelected>>", func_condition1)
cbox_condition2.bind("<<ComboboxSelected>>", func_condition2)

# 定义Label：品番
label_pinfan = tk.Label(windows, text='品番', font=('Arial', 12), width=15, height=2)
label_pinfan.grid(row=1, column=1)
# 定义Label：批号
label_batch = tk.Label(windows, text='批号', font=('Arial', 12), width=15, height=2)
label_batch.grid(row=1, column=2)
# 定义Label：数量
label_quantity = tk.Label(windows, text='数量', font=('Arial', 12), width=15, height=2)
label_quantity.grid(row=1, column=3)
# 定义8个标签Label：存储1-8序号
label_serial_number1 = tk.Label(windows, text='1', font=('Arial', 12), width=4, height=1)
label_serial_number1.grid(row=2, column=0)
label_serial_number2 = tk.Label(windows, text='2', font=('Arial', 12), width=4, height=1)
label_serial_number2.grid(row=3, column=0)
label_serial_number3 = tk.Label(windows, text='3', font=('Arial', 12), width=4, height=1)
label_serial_number3.grid(row=4, column=0)
label_serial_number4 = tk.Label(windows, text='4', font=('Arial', 12), width=4, height=1)
label_serial_number4.grid(row=5, column=0)
label_serial_number5 = tk.Label(windows, text='5', font=('Arial', 12), width=4, height=1)
label_serial_number5.grid(row=6, column=0)
label_serial_number6 = tk.Label(windows, text='6', font=('Arial', 12), width=4, height=1)
label_serial_number6.grid(row=7, column=0)
label_serial_number7 = tk.Label(windows, text='7', font=('Arial', 12), width=4, height=1)
label_serial_number7.grid(row=8, column=0)
label_serial_number8 = tk.Label(windows, text='8', font=('Arial', 12), width=4, height=1)
label_serial_number8.grid(row=9, column=0)

# 品番下拉框 选择事件函数
def func_cbox_pinfan1(event):
    pass

# 定义8个下拉框ComboBox：选择品番
cbox_pinfan1 = tkinter.ttk.Combobox(windows,state='readonly')
cbox_pinfan1.grid(row=2, column=1)
cbox_pinfan2 = tkinter.ttk.Combobox(windows,state='readonly')
cbox_pinfan2.grid(row=3, column=1)
cbox_pinfan3 = tkinter.ttk.Combobox(windows,state='readonly')
cbox_pinfan3.grid(row=4, column=1)
cbox_pinfan4 = tkinter.ttk.Combobox(windows,state='readonly')
cbox_pinfan4.grid(row=5, column=1)
cbox_pinfan5 = tkinter.ttk.Combobox(windows,state='readonly')
cbox_pinfan5.grid(row=6, column=1)
cbox_pinfan6 = tkinter.ttk.Combobox(windows,state='readonly')
cbox_pinfan6.grid(row=7, column=1)
cbox_pinfan7 = tkinter.ttk.Combobox(windows,state='readonly')
cbox_pinfan7.grid(row=8, column=1)
cbox_pinfan8 = tkinter.ttk.Combobox(windows,state='readonly')
cbox_pinfan8.grid(row=9, column=1)
# 定义16个输入框Entry:用来对批号、数量进入输入
## 批号
entry_batch1 = tk.Entry(windows)
entry_batch1.grid(row=2, column=2)
entry_batch2 = tk.Entry(windows)
entry_batch2.grid(row=3, column=2)
entry_batch3 = tk.Entry(windows)
entry_batch3.grid(row=4, column=2)
entry_batch4 = tk.Entry(windows)
entry_batch4.grid(row=5, column=2)
entry_batch5 = tk.Entry(windows)
entry_batch5.grid(row=6, column=2)
entry_batch6 = tk.Entry(windows)
entry_batch6.grid(row=7, column=2)
entry_batch7 = tk.Entry(windows)
entry_batch7.grid(row=8, column=2)
entry_batch8 = tk.Entry(windows)
entry_batch8.grid(row=9, column=2)
## 数量
entry_quantity1 = tk.Entry(windows)
entry_quantity1.grid(row=2, column=3)
entry_quantity2 = tk.Entry(windows)
entry_quantity2.grid(row=3, column=3)
entry_quantity3 = tk.Entry(windows)
entry_quantity3.grid(row=4, column=3)
entry_quantity4 = tk.Entry(windows)
entry_quantity4.grid(row=5, column=3)
entry_quantity5 = tk.Entry(windows)
entry_quantity5.grid(row=6, column=3)
entry_quantity6 = tk.Entry(windows)
entry_quantity6.grid(row=7, column=3)
entry_quantity7 = tk.Entry(windows)
entry_quantity7.grid(row=8, column=3)
entry_quantity8 = tk.Entry(windows)
entry_quantity8.grid(row=9, column=3)
# 定义Label：炉号
label_heat_number = tk.Label(windows, text='炉号：', font=('Arial', 12), width=15, height=2)
label_heat_number.grid(row=10, column=0)
# 定义输入框Entry：用来接收对炉号的输入
entry_heat_number = tk.Entry(windows)
entry_heat_number.grid(row=10, column=1)
# 定义Label：升温时间基准
label_time_base = tk.Label(windows, text='升温时间基准：', font=('Arial', 12), width=15, height=2)
label_time_base.grid(row=11, column=0)
# 定义下拉框ComboBox：用来选择“升温时间基准”
cbox_time_base = tkinter.ttk.Combobox(windows,state='readonly')
cbox_time_base.grid(row=11, column=1)

# 当按钮被点击的时候执行click_button()函数
def click_button():
    base_data = []  # 存储所有数据
    temp_list_pinfan = []   # 存储选择的品番
    base_data.append("%s x %s" % (cbox_condition1.get(),cbox_condition2.get()))  # 设定条件当前选中值
    ## 品番1
    if cbox_pinfan1.get() != '':
        base_data.append(cbox_pinfan1.get())    # 品番1当前选中值
        temp_list_pinfan.append(cbox_pinfan1.get())
    else:
        base_data.append('')
    if entry_batch1.get() != '':
        base_data.append(entry_batch1.get())    # 品番1的批次
    else:
        base_data.append('')
    if entry_quantity1.get() != '':
        base_data.append(entry_quantity1.get())    # 品番1的数量
    else:
        base_data.append('')
    ## 品番2
    if cbox_pinfan2.get() != '':
        base_data.append(cbox_pinfan2.get())    # 品番2当前选中值
        temp_list_pinfan.append(cbox_pinfan2.get())
    else:
        base_data.append('')
    if entry_batch2.get() != '':
        base_data.append(entry_batch2.get())    # 品番2的批次
    else:
        base_data.append('')
    if entry_quantity2.get() != '':
        base_data.append(entry_quantity2.get())    # 品番2的数量
    else:
        base_data.append('')
    ## 品番3
    if cbox_pinfan3.get() != '':
        base_data.append(cbox_pinfan3.get())    # 品番3当前选中值
        temp_list_pinfan.append(cbox_pinfan3.get())
    else:
        base_data.append('')
    if entry_batch3.get() != '':
        base_data.append(entry_batch3.get())    # 品番3的批次
    else:
        base_data.append('')
    if entry_quantity3.get() != '':
        base_data.append(entry_quantity3.get())    # 品番3的数量
    else:
        base_data.append('')
    ## 品番4
    if cbox_pinfan4.get() != '':
        base_data.append(cbox_pinfan4.get())    # 品番4当前选中值
        temp_list_pinfan.append(cbox_pinfan4.get())
    else:
        base_data.append('')
    if entry_batch4.get() != '':
        base_data.append(entry_batch4.get())    # 品番4的批次
    else:
        base_data.append('')
    if entry_quantity4.get() != '':
        base_data.append(entry_quantity4.get())    # 品番4的数量
    else:
        base_data.append('')
    ## 品番5
    if cbox_pinfan5.get() != '':
        base_data.append(cbox_pinfan5.get())    # 品番5当前选中值
        temp_list_pinfan.append(cbox_pinfan5.get())
    else:
        base_data.append('')
    if entry_batch5.get() != '':
        base_data.append(entry_batch5.get())    # 品番5的批次
    else:
        base_data.append('')
    if entry_quantity5.get() != '':
        base_data.append(entry_quantity5.get())    # 品番5的数量
    else:
        base_data.append('')
    ## 品番6
    if cbox_pinfan6.get() != '':
        base_data.append(cbox_pinfan6.get())    # 品番6当前选中值
        temp_list_pinfan.append(cbox_pinfan6.get())
    else:
        base_data.append('')
    if entry_batch6.get() != '':
        base_data.append(entry_batch6.get())    # 品番6的批次
    else:
        base_data.append('')
    if entry_quantity6.get() != '':
        base_data.append(entry_quantity6.get())    # 品番6的数量
    else:
        base_data.append('')
    ## 品番7
    if cbox_pinfan7.get() != '':
        base_data.append(cbox_pinfan7.get())    # 品番7当前选中值
        temp_list_pinfan.append(cbox_pinfan7.get())
    else:
        base_data.append('')
    if entry_batch7.get() != '':
        base_data.append(entry_batch7.get())    # 品番7的批次
    else:
        base_data.append('')
    if entry_quantity7.get() != '':
        base_data.append(entry_quantity7.get())    # 品番7的数量
    else:
        base_data.append('')
    ## 品番8
    if cbox_pinfan8.get() != '':
        base_data.append(cbox_pinfan8.get())    # 品番8当前选中值
        temp_list_pinfan.append(cbox_pinfan8.get())
    else:
        base_data.append('')
    if entry_batch8.get() != '':
        base_data.append(entry_batch8.get())    # 品番8的批次
    else:
        base_data.append('')
    if entry_quantity8.get() != '':
        base_data.append(entry_quantity8.get())    # 品番8的数量
    else:
        base_data.append('')

    # 炉号
    if entry_heat_number.get() != '':
        base_data.append(entry_heat_number.get())
    else:
        base_data.append('')

    # 升温时间基准
    if cbox_time_base.get() != '':
        base_data.append(cbox_time_base.get())
    else:
        base_data.append('')

    print(base_data)
    # 判断 品番是否有重复值
    # print(temp_list_pinfan)
    if len(temp_list_pinfan) > len(set(temp_list_pinfan)):
        messagebox.showwarning(title='警告', message='品番选择有重复！')
        return

    # 使用消息对话框控件，showinfo()表示温馨提示
    result = messagebox.askokcancel(title='提示', message='请确定是否生成Excel文件！')
    print(result)
    if result == True:
        write_excel(base_data)
    else:
        return


# 定义一个按钮：生成
button_create = tk.Button(windows,
                          text='生成',
                          #bg='#7CCD7C',
                          font=('Arial', 12),
                          width=12, height=2,
                          command=click_button)
button_create.grid(row=11, column=3)


# 把数据写入Excel文件
def write_excel(data):
    # 1.根据日期时间生成目标文件名
    target_filename ='{0:%Y%m%d-%H%M%S}'.format(datetime.datetime.now())
    # 复制模板文件到Save文件夹
    shutil.copy('模板文件.xlsx','Save\\{}'.format(target_filename+'.xlsx'))
    full_filename = "Save\\{}".format(target_filename+'.xlsx')
    if os.path.exists("Save\\{}".format(target_filename+'.xlsx')):
        print("目标文件复制成功！")
        wb = load_workbook(full_filename)
        ws = wb.active
        ws.title = "加热确认表"  # 更改表名
        # 把数据写入文件
        ws['D12'] = str(data[0]) # 写入条件
        ws['C3'] = str(data[1]) # 写入品番1
        ws['G3'] = str(data[2]) # 写入品番1批次
        ws['J3'] = str(data[3]) # 写入品番1数量
        ws['C4'] = str(data[4]) # 写入品番2
        ws['G4'] = str(data[5]) # 写入品番2批次
        ws['J4'] = str(data[6]) # 写入品番2数量
        ws['C5'] = str(data[7]) # 写入品番3
        ws['G5'] = str(data[8]) # 写入品番3批次
        ws['J5'] = str(data[9]) # 写入品番3数量
        ws['C6'] = str(data[10]) # 写入品番4
        ws['G6'] = str(data[11]) # 写入品番4批次
        ws['J6'] = str(data[12]) # 写入品番4数量
        ws['C7'] = str(data[13]) # 写入品番5
        ws['G7'] = str(data[14]) # 写入品番5批次
        ws['J7'] = str(data[15]) # 写入品番5数量
        ws['C8'] = str(data[16]) # 写入品番6
        ws['G8'] = str(data[17]) # 写入品番6批次
        ws['J8'] = str(data[18]) # 写入品番6数量
        ws['C9'] = str(data[19]) # 写入品番7
        ws['G9'] = str(data[20]) # 写入品番7批次
        ws['J9'] = str(data[21]) # 写入品番7数量
        ws['C10'] = str(data[22]) # 写入品番8
        ws['G10'] = str(data[23]) # 写入品番8批次
        ws['J10'] = str(data[24]) # 写入品番8数量
        ws['A12'] = str(data[25]) # 写入炉号
        ws['A16'] = str(data[26]) # 写入升温时间基准
        ws['J17'] = '{0:%Y-%m-%d}'.format(datetime.datetime.now())  # 写入当前日期


        for i in range(3,14):
            ws.row_dimensions[i].height = 22
        wb.save(full_filename)
        wb.close()
        os.startfile(full_filename)  # 调用系统默认程序打开，输出的Excel文件

    else:
        print("目标文件复制不成功！")
        messagebox.showwarning(title='警告', message='文件生成不成功，请重新生成！')


conn.close()


windows.mainloop()

